package com.hitrust.gs.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import system.base.OutSubHisSearchEvent;

import com.hitrust.gs.bean.GSOutSubHisBean;
import com.hitrust.gs.bean.GSOutSubHisWarehouseBean;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;

import framework.core.Conditions;
import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBResult;
import framework.core.ObjectList;

public class GSOutSubHisBaeanDAO extends GSBaseDAO{

	public final static String tableName = "T_OUT_SUB_HIS_";
	
	public void createTableByYear(String year) throws Exception{
		String startTime = year+"-01-01 00:00:00";
		String endTIme = year+"-12-31 23:59:59";
		String outSubTableName = tableName+year;
		
		String sqlConfig = "create table "+ outSubTableName + " as ("+
	    "select distinct(head.warehouse_code) warehouse_code from t_out_head_his head  "
		+"where head.out_time >= '"+startTime+"' and head.out_time <= '"+endTIme+"' )" ;
	    try {
	       this.executeUpdate(sqlConfig, null);
	    } catch (Exception e) {
	    	e.printStackTrace();
	        GSLogWriter.error(this,e.toString());
	        throw new GSException(GSException.E00001, GSException.E00001_MSG);
	    }
		
		List list =findOutSubWarehouseByTime(startTime,endTIme);
		for (int i = 0; i < list.size(); i++) {
			GSOutSubHisWarehouseBean bean = (GSOutSubHisWarehouseBean) list.get(i);
			String sql = "create table "+ outSubTableName+"_"+bean.getWarehouseCode() + " as ("+
			"select head.WAREHOUSE_NAME,sub.POSITION_NAME,sub.OUT_BILL_CODE,head.OUT_TIME,head.CUSTOMER_NAME,head.OUT_TYPE,"
				+"sub.GET_BILL_CODE,sub.PROVIDER_CODE,head.REPAIR_BILL_CODE,sub.CUSTOMER_BUS_CODE,head.BALANCE_FLAG,sub.PRODUCT_CODE,"
				+"product.NAME product_name,product.product_unit,product.SPEC,product.MODEL,product.PRODUCT_BRAND,sub.PRODUCT_COUNT,sub.PRODUCT_PRICE,"
				+"sub.PRODUCT_MONEY,sub.GET_CUSTOMER_NAME,sub.GET_CUSTOMER_STAFF_NAME,sub.BALANCE_MODE,sub.CREATE_USER,sub.CREATE_TIME,"
				+"sub.SUB_REMARK,product.CATEGORY_CODE,head.warehouse_code,"
				+"provider.NAME PROVIDER_NAME,sub.STATUS,sub.product_id,product.IS_NORMAL,sub.out_bill_id,sub.POSITION_CODE  "
				+"from t_out_sub_his sub left join t_out_head_his head on sub.OUT_BILL_ID = head.ID "
				+"left join t_product product on sub.PRODUCT_ID = product.ID left join t_provider provider on sub.PROVIDER_CODE = provider.CODE "
				+"where head.out_time >= '"+startTime+"' and head.out_time <= '"+endTIme+"' and head.warehouse_code = '"+bean.getWarehouseCode()+"' )" ;
	        try {
	           this.executeUpdate(sql, null);
	        } catch (Exception e) {
	        	e.printStackTrace();
	            GSLogWriter.error(this,e.toString());
	            throw new GSException(GSException.E00001, GSException.E00001_MSG);
	        }
	        Thread.sleep(100);
		}
		
		
	}
	
	public List findOutSubWarehouseByTime(String startTime,String endTime) throws Exception{
		String warehouseSql = "select distinct(head.warehouse_code) warehouse_code from t_out_head_his head where 1=1 ";
		if(startTime.equals("")&&!endTime.equals("")){
			warehouseSql += " and head.out_time <= '"+endTime+"'";
		}else if(!startTime.equals("")&&endTime.equals("")){
			warehouseSql += " and head.out_time >= '"+startTime+"'";
		}else if (!startTime.equals("")&&!endTime.equals("")){
			warehouseSql += " and head.out_time >= '"+startTime+"' and head.out_time <= '"+endTime+"'";
		}
		ArrayList list =this.executeQuery(new GSOutSubHisWarehouseBean(), warehouseSql,null);
		return list==null?new ArrayList():list;
	}
	
	public List findOutSubWarehouseByTableName(String yearTableName) throws Exception{
		String warehouseSql = "select distinct(warehouse_code) warehouse_code from "+yearTableName+" ";
		ArrayList list =this.executeQuery(new GSOutSubHisWarehouseBean(), warehouseSql,null);
		return list==null?new ArrayList():list;
	}
	
	public List findOutSubByEvent(OutSubHisSearchEvent event) throws Exception{
		List returnList = new ArrayList();
		String tableName = this.tableName+event.getStartOutTime().substring(0,4);
		List warehouseList =findOutSubWarehouseByTableName(tableName);
		List warehouseListCodes = new ArrayList();
		for (int i = 0; i < event.getWarehouseCodes().length; i++) {
			for (int j = 0; j < warehouseList.size(); j++) {
				GSOutSubHisWarehouseBean bean = (GSOutSubHisWarehouseBean) warehouseList.get(j);
					if(bean.getWarehouseCode().equals(event.getWarehouseCodes()[i])){
						warehouseListCodes.add(event.getWarehouseCodes()[i]);
					}
			}
		}
		
		if(event.getWarehouseCodes()!=null){
			for (int i = 0; i < warehouseListCodes.size(); i++) {
				List list=new ArrayList();
				
				String sql = "select * from "+tableName+"_"+warehouseListCodes.get(i) + " outSubHis where 1=1 ";
				sql = toSql(event, sql); 
				 Object[] obj={};
			        try {
			            list=this.executeQuery(new GSOutSubHisBean(), sql, obj);
			        } catch (Exception e) {
			            GSLogWriter.error(this,e.toString());
			            throw new GSException(GSException.E10040, GSException.E10040_MSG);
			        }
			    for (int j = 0; j < list.size(); j++) {
			    	returnList.add(list.get(j));
				}
			    Thread.sleep(100);
			}
		}
		return returnList;
	}

	public String toSql(OutSubHisSearchEvent event, String sql) {
		if(!event.getProviderName().equalsIgnoreCase("")){
			sql += " and outSubHis.provider_name like '%"+event.getProviderName()+"%'";
		}
		if(!event.getOutBillCode().equalsIgnoreCase("")){
			sql +=" and outSubHis.out_bill_code = '"+event.getOutBillCode()+"'";
		}
		if(!event.getRepairBillCode().equalsIgnoreCase("")){
			sql +=" and outSubHis.repair_bill_code = '"+event.getRepairBillCode()+"'";
		}
		if(!event.getCustomerBusCode().equalsIgnoreCase("")){
			sql +=" and outSubHis.customer_bus_code = '"+event.getCustomerBusCode()+"'";
		}
		if (!event.getOutType().equalsIgnoreCase("")){
			sql +=" and outSubHis.out_type = '"+event.getOutType()+"'";
		}
		if(!event.getBalanceMode().equalsIgnoreCase("")){
			sql +=" and outSubHis.balance_mode = '"+event.getBalanceMode()+"'";
		}
	    if (!event.getKeeperStaffId().equalsIgnoreCase("")) {
	    	sql +=" and outSubHis.product_id in (select product_id from T_WAREHOUSE_PRODUCT where keeper_staff_id = '"+event.getKeeperStaffId()+"')";
	    }
	    if(!event.getProductCode().equalsIgnoreCase("")){
	    	sql += " and outSubHis.product_code = '"+event.getProductCode()+"'";
	    }
	    if(!event.getProductName().equalsIgnoreCase("")){
	    	sql +=" and outSubHis.product_name like '%"+event.getProductName()+"%'";
	    }
	    if(!event.getStatus().equalsIgnoreCase("")){
	    	sql +=" and outSubHis.status = '"+event.getStatus()+"'";
	    }
	    if(!event.getCustomerName().equalsIgnoreCase("")){
	    	sql +=" and outSubHis.customer_name = '"+event.getCustomerName()+"'";
	    }
	    if(!event.getGetCustomerName().equalsIgnoreCase("")){
	    	sql +=" and outSubHis.get_customer_name = '"+event.getGetCustomerName()+"'";
	    }
	    if(!event.getStartOutTime().equalsIgnoreCase("")){
	    	sql +=" and outSubHis.out_time >= '"+event.getStartOutTime()+"'";
	    }
	    if(!event.getEndOutTime().equalsIgnoreCase("")){
	    	sql +=" and outSubHis.out_time <= '"+event.getEndOutTime()+"'";
	    }
	    if(!event.getIsNormal().equalsIgnoreCase("")){
	    	sql +=" and outSubHis.IS_NORMAL = '"+event.getIsNormal()+"'";
	    }
		
		String categorycode3 = event.getGrandfatherCategoryCode();
	    String categorycode2 = event.getUpperCategoryCode();
	    String categorycode1 = event.getCategoryCode();
	    if (categorycode3== null || categorycode3.equalsIgnoreCase("null") || categorycode3.equalsIgnoreCase("")) 
	    {
	        categorycode3 = "";
	        event.setGrandfatherCategoryCode(categorycode3);
	    }    
	    if (categorycode2== null || categorycode2.equalsIgnoreCase("null") || categorycode2.equalsIgnoreCase(""))
	    {
	        categorycode2 = "";
	        event.setUpperCategoryCode(categorycode2);
	    }
	    if (categorycode1== null || categorycode1.equalsIgnoreCase("null") || categorycode1.equalsIgnoreCase("")) 
	    {
	        categorycode1 = "";
	        event.setCategoryCode(categorycode1);
	    }
	    if (!categorycode1.equals(""))
	    {
            sql+="and outSubHis.product_code in (select distinct a.concentricity_code from t_product a where a.category_code= '"+categorycode1+"') ";
	    }
	    else if (!categorycode2.equals(""))
	    {
	    	sql+="and outSubHis.product_code  in (select distinct a.concentricity_code from t_product a  join t_product_category b  on a.category_code = b.code where b.CATEGORY_ID2= '"+categorycode2+"' or b.code='"+categorycode2+"') ";
	    }
	    else if (!categorycode3.equals(""))
	    {
	    	sql+="and outSubHis.product_code  in (select distinct a.concentricity_code from t_product a  join t_product_category b  on a.category_code = b.code where b.CATEGORY_ID1= '"+categorycode3+"' or b.code='"+categorycode3+"') ";
	    }
		return sql;
	}
	
	   public ObjectList searchByHis(DBConnection con,OutSubHisSearchEvent event) throws Exception{
	        DBEngine.checkCon(con);
	        ObjectList list = ObjectList.getList();
			int total=0;
			if(event.getWarehouseCodes()!=null&&!event.getStartOutTime().trim().equals("")){
				String tableName = this.tableName+event.getStartOutTime().substring(0,4);
				List warehouseList =findOutSubWarehouseByTableName(tableName);
				List warehouseListCodes = new ArrayList();
				for (int i = 0; i < event.getWarehouseCodes().length; i++) {
					for (int j = 0; j < warehouseList.size(); j++) {
						GSOutSubHisWarehouseBean bean = (GSOutSubHisWarehouseBean) warehouseList.get(j);
							if(bean.getWarehouseCode().equals(event.getWarehouseCodes()[i])){
								warehouseListCodes.add(event.getWarehouseCodes()[i]);
							}
					}
				}
				for (int i = 0; i < warehouseListCodes.size(); i++) {
					
					String sql = "select  count(outSubHis.product_id)  from "+tableName+"_"+warehouseListCodes.get(i) + " outSubHis where 1=1";
					sql = toSql(event, sql);
					
				    int rsCount = DBEngine.doCount(con, sql);
				    total+= rsCount;
				}
				int num = event.getPageNum();
				if(num>10){
					event.setPageNum(10);
				}
		        int size = event.getPageSize();
		        for (int i = 0; i < warehouseListCodes.size()&&list.size()<20; i++) {
					if(list.size()<=event.getPageSize()){
						String sql = "select * from "+tableName+"_"+warehouseListCodes.get(i)+ " outSubHis where 1=1";
						sql = toSql(event, sql); 
						
						event.setPageNum(num);

				        ObjectList objectList = ObjectList.getList();
				        DBResult result = DBEngine.doSelectWithOutOrder(con,sql,event);
				        objectList = ObjectList.fromRsMiniByProcess(new GSOutSubHisBean(),result.getRs(),size!=event.getPageSize()?size:event.getPageSize(),event.getPageNum(), total);
				        result.clear();
				        for (int j = 0; j < objectList.size(); j++) {
							list.add(objectList.get(j));
						}
				        size = size - objectList.size();
					}else{
						continue;
					}
					
					
				}
		        list.setTotalSize(total);
		        list.setPageSize(event.getPageSize());
		        list.setPageNum(list.getPageNum(event.getPageSize(),event.getPageNum(),total)); 
		        list.setTotalPages(list.getTotalPages(list.getPageSize(),list.getTotalSize()));
		        if(list.getTotalPage()==0){
		        	list.setPageNum(0);
		        }
			}
	        return list;
	   }
	   
	   public String getOutMonty(DBConnection con,OutSubHisSearchEvent event) throws Exception{
		   double monty = 0;
		   if(event.getWarehouseCodes()!=null&&!event.getStartOutTime().trim().equals("")){
				String tableName = this.tableName+event.getStartOutTime().substring(0,4);
				List warehouseList =findOutSubWarehouseByTableName(tableName);
				List warehouseListCodes = new ArrayList();
				for (int i = 0; i < event.getWarehouseCodes().length; i++) {
					for (int j = 0; j < warehouseList.size(); j++) {
						GSOutSubHisWarehouseBean bean = (GSOutSubHisWarehouseBean) warehouseList.get(j);
							if(bean.getWarehouseCode().equals(event.getWarehouseCodes()[i])){
								warehouseListCodes.add(event.getWarehouseCodes()[i]);
							}
					}
				}
				for (int i = 0; i < warehouseListCodes.size(); i++) {
					
					String sql = "select  sum(outSubHis.Product_Money)  from "+tableName+"_"+warehouseListCodes.get(i) + " outSubHis where 1=1";
					sql = toSql(event, sql);
					sql += " and outSubHis.status = 1 ";
				    DBResult dbrs = DBEngine.doSelect(con, sql);
				    ResultSet rs = dbrs.getRs();
				    if(rs.next()){
				    	monty += rs.getDouble(1);    
			        }
				    dbrs.clear();
				}
				
			}
		   return String.valueOf(monty);
	   }
}
